package com.ehualu.javahadoop.until;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;

/**
 * @Author: 吴敬超
 * @Date: 2020/3/2 14:16
 */
public class HiveUntil {


    /*
    * dt 数据日期
    * starttime 开始时间
    * endtime 结束时间
    * kks  卡口
    * */
    public Map<String,String> hivenum(String dt,String starttime,String endtime,String kks) {


        Map configmap = ReadConfigurationClass.readConfiguration("application.properties");

        String driver = configmap.get("dbdriver").toString();
        String dburl = configmap.get("dburl").toString();
        String dbusername = configmap.get("dbusername").toString();
        String dbpassword = configmap.get("dbpassword").toString();

        Map<String, String> info = new HashMap<String, String>();
        Connection con = null;

        Statement stmt = null;

        ResultSet res = null;
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(dburl, dbusername, dbpassword);
            stmt = con.createStatement();

            String sql = null;

            if (kks.equals("all")){
                sql = String.format("select t.sbbh, count( DISTINCT(t.qxdtid)) as ct from gcsjdb.lcgcsj t  where t.dt in(%s) and (t.cjsj>='%s' and t.cjsj<='%s') group by t.sbbh ",dt,starttime,endtime);

            }else {

                sql = String.format("select t.sbbh, count( DISTINCT(t.qxdtid)) as ct from gcsjdb.lcgcsj t  where t.dt in(%s) and (t.cjsj>='%s' and t.cjsj<='%s') group by t.sbbh having t.sbbh in(%s)",dt,starttime,endtime,kks);

            }

//            String sql = "select count(*) as ct from gcsjdb.lcgcsj where dt=20190801 and qx=371503";

            System.out.println(sql);
            res = stmt.executeQuery(sql);

//        System.out.println(res.getString(1));
            while (res.next()) {
//                num = res.getString(1);

                String sbbh=res.getString("sbbh");
                String num = res.getString("ct");

                System.out.println("sbbh:" + sbbh);

                System.out.println("sbbh_num:" + num);

                info.put(sbbh,num);


            }


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (res != null) {
                try {
                    res.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }

        return info;
//        return tablename;
    }


}
